const connection = require('../../sql');

class MaterialTypeService {
  async createMaterialType({
    parent_id = 0,
    category_code,
    category_name,
    category_level,
    sort,
    remark,
    status,
    create_id,
    updata_id
  }) {
    try {
      let finalLevel = category_level || 1; // 默认一级
      if (parent_id !== 0) {
        // 验证 parent_id 存在
        const [parentResult] = await connection.execute('SELECT category_level FROM basic_material_type WHERE id = ?', [
          parent_id
        ]);
        if (parentResult.length === 0) {
          throw new Error('上级分类不存在');
        }
        finalLevel = parentResult[0].category_level + 1; // 子级 = 父级 + 1
        if (category_level && category_level !== finalLevel) {
          throw new Error('分类层级与父级不匹配');
        }
      }

      const now = new Date();
      const sql = `
        INSERT INTO basic_material_type (
          parent_id, category_code, category_name, category_level,
          sort, remark, status, create_id, updata_id, create_at, updata_at
        )
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      `;
      const [result] = await connection.execute(sql, [
        parent_id,
        category_code || null,
        category_name,
        finalLevel,
        sort !== undefined ? sort : null,
        remark || null,
        status !== undefined ? status : null,
        create_id,
        updata_id,
        now,
        now
      ]);
      return { id: result.insertId };
    } catch (error) {
      throw error;
    }
  }

  async deleteMaterialTypes(ids) {
    try {
      // 检查是否有子分类
      const checkSql = `
        SELECT id FROM basic_material_type
        WHERE parent_id IN (${ids.map(() => '?').join(',')})
        LIMIT 1
      `;
      const [checkResult] = await connection.execute(checkSql, ids);
      if (checkResult.length > 0) {
        throw new Error('存在子分类，无法删除');
      }

      const sql = `
        DELETE FROM basic_material_type
        WHERE id IN (${ids.map(() => '?').join(',')})
      `;
      const [result] = await connection.execute(sql, ids);
      if (result.affectedRows === 0) {
        throw new Error('没有可删除的分类');
      }
      return { success: true };
    } catch (error) {
      throw error;
    }
  }

  async updateMaterialType({ id, parent_id = 0, category_code, category_name, category_level, sort, remark, status, updata_id }) {
    try {
      let finalLevel = category_level || 1;
      if (parent_id !== 0) {
        const [parentResult] = await connection.execute('SELECT category_level FROM basic_material_type WHERE id = ?', [
          parent_id
        ]);
        if (parentResult.length === 0) {
          throw new Error('上级分类不存在');
        }
        finalLevel = parentResult[0].category_level + 1;
        if (category_level && category_level !== finalLevel) {
          throw new Error('分类层级与父级不匹配');
        }
      }

      const sql = `
        UPDATE basic_material_type
        SET
          parent_id = ?, category_code = ?, category_name = ?,
          category_level = ?, sort = ?, remark = ?, status = ?,
          updata_id = ?, updata_at = ?
        WHERE id = ?
      `;
      const now = new Date();
      const [result] = await connection.execute(sql, [
        parent_id,
        category_code || null,
        category_name,
        finalLevel,
        sort !== undefined ? sort : null,
        remark || null,
        status !== undefined ? status : null,
        updata_id,
        now,
        id
      ]);
      if (result.affectedRows === 0) {
        throw new Error('分类不存在');
      }
      return { success: true };
    } catch (error) {
      throw error;
    }
  }

  async getMaterialTypeList({ page, limit, category_name, category_code }) {
    const offset = (page - 1) * limit;
    let whereClause = '';
    const params = [];

    if (category_name) {
      whereClause += ' AND m.category_name LIKE ?';
      params.push(`%${category_name}%`);
    }
    if (category_code) {
      whereClause += ' AND m.category_code LIKE ?';
      params.push(`%${category_code}%`);
    }

    const countSql = `
      SELECT COUNT(*) as count
      FROM basic_material_type m
      WHERE parent_id = 0 ${whereClause}
    `;
    const allSql = `
      SELECT 
        m.id, m.parent_id, m.category_code, m.category_name,
        m.category_level, m.sort, m.remark, m.status,
        m.create_id, m.create_at, m.updata_id, m.updata_at,
        creator.username AS create_username,
        updater.username AS updata_username
      FROM basic_material_type m
      LEFT JOIN sys_user creator ON m.create_id = creator.id AND creator.del_flag = 'F'
      LEFT JOIN sys_user updater ON m.updata_id = updater.id AND updater.del_flag = 'F'
      WHERE 1=1 ${whereClause}
      ORDER BY m.sort ASC, m.id ASC
    `;

    try {
      const [countResult] = await connection.execute(countSql, params);
      const [allRows] = await connection.execute(allSql, params);

      // 构建树状结构
      const treeData = buildTree(allRows);
      const topLevelRows = treeData.filter(row => row.parent_id === 0);
      const paginatedRows = topLevelRows.slice(offset, offset + limit);

      return {
        current: page,
        limit,
        totalPage: Math.ceil(countResult[0].count / limit) || 1,
        total: countResult[0].count,
        rows: paginatedRows
      };
    } catch (error) {
      throw error;
    }
  }
  async updateMaterialTypeState({ id, newState, updata_id }) {
    try {
      const now = new Date();
      let sql;
      let params;

      if (newState === 1017002) {
        // Disable: Update parent and all descendants
        sql = `
          UPDATE basic_material_type
          SET status = ?, updata_id = ?, updata_at = ?
          WHERE id IN (
            WITH RECURSIVE category_tree AS (
              SELECT id
              FROM basic_material_type
              WHERE id = ?
              UNION ALL
              SELECT t.id
              FROM basic_material_type t
              INNER JOIN category_tree ct ON t.parent_id = ct.id
            )
            SELECT id FROM category_tree
          )
        `;
        params = [newState, updata_id, now, id];
      } else {
        // Enable: Update only the specified ID
        sql = `
          UPDATE basic_material_type
          SET status = ?, updata_id = ?, updata_at = ?
          WHERE id = ?
        `;
        params = [newState, updata_id, now, id];
      }
      const [result] = await connection.execute(sql, params);

      if (result.affectedRows === 0) {
        throw new Error('没有可更新的分类');
      }
      return { success: true };
    } catch (error) {
      throw error;
    }
  }
  async batchCreateMaterialType({ parent_id, data, create_id, updata_id }) {
    try {
      // 验证 parent_id
      const [parentResult] = await connection.execute('SELECT category_level FROM basic_material_type WHERE id = ?', [parent_id]);
      if (parentResult.length === 0) {
        throw new Error('上级分类不存在');
      }
      const category_level = parentResult[0].category_level + 1;

      // 验证 category_code 唯一性（若需要）
      const codes = data.map(item => item.category_code).filter(code => code);
      if (codes.length > 0) {
        const checkSql = `
          SELECT category_code FROM basic_material_type
          WHERE category_code IN (${codes.map(() => '?').join(',')})
        `;
        const [existingCodes] = await connection.execute(checkSql, codes);
        if (existingCodes.length > 0) {
          throw new Error(`分类编码重复: ${existingCodes.map(c => c.category_code).join(', ')}`);
        }
      }

      // 构建批量插入 SQL
      const now = new Date();
      const sql = `
        INSERT INTO basic_material_type (
          parent_id, category_code, category_name, category_level,
          sort, remark, status, create_id, updata_id, create_at, updata_at
        )
        VALUES ${data.map(() => '(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)').join(', ')}
      `;
      const params = data.flatMap(item => [
        parent_id,
        item.category_code || null,
        item.category_name,
        category_level,
        item.sort !== undefined ? item.sort : null,
        item.remark || null,
        1017001, // 默认启用
        create_id,
        updata_id,
        now,
        now
      ]);
      const [result] = await connection.execute(sql, params);

      // 计算插入的 ID 范围
      const ids = Array.from({ length: result.affectedRows }, (_, i) => result.insertId + i);

      return { ids };
    } catch (error) {
      throw error;
    }
  }
  async getMaterialTypeCascade({ keyword }) {
    let whereClause = 'WHERE m.status = 1017001';
    const params = [];

    if (keyword) {
      whereClause += ' AND m.category_name LIKE ?';
      params.push(`%${keyword}%`);
    }

    const sql = `
      SELECT 
        m.id, m.parent_id, m.category_name
      FROM basic_material_type m
      LEFT JOIN sys_user creator ON m.create_id = creator.id AND creator.del_flag = 'F'
      LEFT JOIN sys_user updater ON m.updata_id = updater.id AND updater.del_flag = 'F'
      ${whereClause}
      ORDER BY m.sort ASC, m.id ASC
    `;

    try {
      const [rows] = await connection.execute(sql, params);
      const treeData = buildCascadeTree(rows);
      return treeData;
    } catch (error) {
      throw error;
    }
  }
}

// 构建树状结构
function buildTree(rows) {
  const map = {};
  const tree = [];

  // 初始化 map
  rows.forEach(row => {
    map[row.id] = { ...row, children: [] };
  });

  // 构建树
  rows.forEach(row => {
    if (row.parent_id === 0 || !map[row.parent_id]) {
      tree.push(map[row.id]);
    } else {
      map[row.parent_id].children.push(map[row.id]);
    }
  });

  return tree;
}

//构建级联树状结构
function buildCascadeTree(rows) {
  const map = {};
  const tree = [];

  rows.forEach(row => {
    map[row.id] = {
      value: row.id,
      label: row.category_name,
      children: []
    };
  });

  rows.forEach(row => {
    if (row.parent_id === 0 || !map[row.parent_id]) {
      tree.push(map[row.id]);
    } else {
      map[row.parent_id].children.push(map[row.id]);
    }
  });

  return tree;
}

module.exports = new MaterialTypeService();
